Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Configuration
Imports System.Text
Module basUtils
    'Attribute VB_Name = "basUtils"
    Public sRetErrormsg As String
    Public Function CheckString(ByVal sStringName As String) As String

        'This function ensures strings have valid quotes around them for
        'insertion into a database.

        sStringName = Replace(sStringName, "'", "''")

        sStringName = "'" & sStringName & "'"
        CheckString = sStringName

    End Function

    Public Function SafeString(ByVal sStringName As Object) As String
        If IsNothing(sStringName) Then
            SafeString = ""
        Else
            SafeString = Trim(sStringName)
        End If
    End Function


    Public Sub LogErrorEvent(ByVal sOrigin As String)
        Dim sErrMsg As String

        sErrMsg = ""
        sErrMsg = sErrMsg & "Usernamage." & sOrigin & vbCrLf
        sErrMsg = sErrMsg & "Source: " & Err.Source & vbCrLf
        sErrMsg = sErrMsg & "Number: " & Err.Number & vbCrLf
        sErrMsg = sErrMsg & "Description: " & Err.Description
        sRetErrormsg = sErrMsg
        'Debug.Print(sErrMsg)
        'App.LogEvent(sErrMsg, vbLogEventTypeError)
    End Sub


    Public Function BoolToYN(ByVal b As Boolean) As String
        If b Then
            BoolToYN = "Y"
        Else
            BoolToYN = "N"
        End If
    End Function

    Public Sub GetDataset(ByVal strLO As String, ByVal strSQL As String, ByVal strTblName As String, ByRef strError As String, ByRef oDS As DataSet)
        ' This subroutine takes in the environment(D for development P for production)
        ' the user ID, SQL needed for query 
        Dim cmd As SqlDataAdapter
        Dim Ds As New DataSet
        

        Dim Conn As New SqlConnection(strLO)

        'Conn.ConnectionTimeout = 240

        cmd = New SqlDataAdapter(strSQL, Conn)
        cmd.SelectCommand.CommandTimeout = 0

        Try
            cmd.Fill(oDS, strTblName)
        Catch ex As SqlException

            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            'strError = ex.Message '& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            sRetErrormsg = ExpError(strTDErr, ex)


        Finally
            If Not (Conn Is Nothing) Then
                Conn.Close()
            End If
        End Try
        ' below 2  lines for testing purposes only
        'Dim intCnt As Integer
        'intCnt = oDS.Tables(strTblName).Rows.Count
    End Sub

    Public Sub CallExecuteSQL(ByVal strLO As String, ByVal strSQL As String, ByRef strError As String)
        ' This subroutine takes in the environment(D for development P for production)
        ' the user ID, SQL needed for query 
        Dim cmd As SqlCommand
        Dim Ds As New DataSet
        'Dim strLO As String
        'TDUID = UserID
        'strLO = GetConnectstring()
        Dim Conn As New SqlConnection(strLO)
        'Conn.ConnectionTimeout = 240
        cmd = New SqlCommand(strSQL, Conn)
        cmd.CommandTimeout = 0

        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        Catch ex As SqlException
            Dim strErrEx As String = ""
            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            'strError = ex.Message ''& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            sRetErrormsg = ExpError(strTDErr, ex)

        Finally
            If Not (Conn Is Nothing) Then
                Conn.Close()
            End If
        End Try

    End Sub

    Public Function GetDataReader(ByVal strLO As String, ByVal strSQL As String, ByRef strError As String) As SqlDataReader


        Dim Conn As New SqlConnection(strLO)
        'Conn.ConnectionTimeout = 240
        Dim cmd As New SqlCommand(strSQL, Conn)
        Dim reader As SqlDataReader = Nothing
        cmd.CommandType = CommandType.Text
        cmd.CommandTimeout = 0
        Try
            Conn.Open()
            reader = cmd.ExecuteReader
            'If Not IsNothing(reader) Then
            '    GetDataReader = reader
            'Else
            '    Return Nothing
            'End If
            'Return reader
            'GetDataReader = reader
        Catch ex As SqlException
            'Dim strErrEx As String
            Dim strTDErr As String = ""
            Dim i As Integer

            For i = 0 To ex.Errors.Count - 1
                strTDErr = ex.Errors(i).Message.ToString()
            Next i
            ''/**use this code to debug**//
            'strError = ex.Message '& vbCrLf & " UserID :" & UserID & " Offending SQL error " & strTDErr & " : " & vbCrLf & strSQL & vbCrLf
            sRetErrormsg = ExpError(strTDErr, ex)

        End Try
        If Not IsNothing(reader) Then
            GetDataReader = reader
        Else
            Return Nothing
        End If
    End Function
    Private Function ExpError(ByVal strTDErr As String, ByVal ex As SqlException) As String

        'Dim strError As String
        Dim strErrEx As String

        Select Case strTDErr
            Case "-3004"
                strErrEx = " User Access not authorized. "
            Case "-3003"
                strErrEx = "Invalid  User Access. "
            Case "-2803"
                strErrEx = "Duplicate key entry on insert. Please report page. "
            Case "0"
                'strErrEx = "Teradata Timeout has occurred. "
                strErrEx = "database is busy with other process. Wait a few minutes and retry your query."
            Case "-3706"
                strErrEx = "SQL Syntax Error. Please report page and search criteria used. "
            Case "-3707"
                strErrEx = "SQL Syntax Error. Please report page and search criteria used. "
            Case Else
                strErrEx = "Database Error. Please report page and search criteria used. "
        End Select
        Return strErrEx
    End Function
    Public Function ReturnErr() As String
        Return sRetErrormsg
    End Function
End Module
